﻿using System;
using System.Collections.Generic;
using System.Data;
using ControleRepresentante.Data.Catalogs;
using ControleRepresentante.Data.Helpers;

namespace ControleRepresentante.Data.CatalogSteps
{
    class UsuarioHolderManyToManySystemUser : CatalogStepBase<ControleRepresentanteCatalogStepTypes>
    {
        protected override ControleRepresentanteCatalogStepTypes CatalogStepType
        {
            get { return ControleRepresentanteCatalogStepTypes.UsuarioHolderManyToManySystemUser; }
        }

        public override void Execute(IDataAccess dataAccess)
        {
            new CatalogStepHelper(dataAccess).CreateTableToTable("FiltrosToUsers",
                "UsuariosHolderId", ColumnType.Guid, "UsuariosHolder", "DetailsHolderId",
                "UserId", ColumnType.Guid, "SystemUser", "Id");

            List<UserFiltros> userFiltros = new List<UserFiltros>();
            using (IDataReader reader = dataAccess.ExecuteReader(@"
                SELECT Id, FabricaFiltroId, ClienteFiltroId, VendedorFiltroId, TransportadoraFiltroId
                FROM SystemUser"))
            {
                while (reader.Read())
                {
                    Guid userId = (Guid) reader["Id"];
                    Guid? fabricaId = reader.GetNullableValue<Guid>("FabricaFiltroId");
                    Guid? clienteId = reader.GetNullableValue<Guid>("ClienteFiltroId");
                    Guid? vendedorId = reader.GetNullableValue<Guid>("VendedorFiltroId");
                    Guid? transportadoraId = reader.GetNullableValue<Guid>("TransportadoraFiltroId");
                    userFiltros.Add(new UserFiltros(userId, fabricaId, clienteId, vendedorId, transportadoraId));
                }
            }

            foreach (UserFiltros userFiltro in userFiltros)
            {
                Parameter userIdParameter = new Parameter("UserId", ParameterType.Guid, userFiltro.Id);
                AddFiltrosToUsers(dataAccess, userFiltro.FabricaId, userIdParameter);
                AddFiltrosToUsers(dataAccess, userFiltro.ClienteId, userIdParameter);
                AddFiltrosToUsers(dataAccess, userFiltro.VendedorId, userIdParameter);
                AddFiltrosToUsers(dataAccess, userFiltro.TransportadoraId, userIdParameter);
            }

            new CatalogStepHelper(dataAccess).GetTable("SystemUser")
                .DropForeignKey("systemuser_ibfk_2")
                .DropForeignKey("systemuser_ibfk_3")
                .DropForeignKey("systemuser_ibfk_4")
                .DropForeignKey("systemuser_ibfk_5")
                .DropColumn("FabricaFiltroId")
                .DropColumn("ClienteFiltroId")
                .DropColumn("VendedorFiltroId")
                .DropColumn("TransportadoraFiltroId");
        }

        private static void AddFiltrosToUsers(IDataAccess dataAccess, Guid? usuariosHolderId, Parameter userIdParameter)
        {
            if (usuariosHolderId.HasValue)
            {
                dataAccess.ExecuteCommand(@"
                    INSERT INTO FiltrosToUsers(UsuariosHolderId, UserId)
                    VALUES(@UsuarioHolderId, @UserId)",
                    new Parameter("UsuarioHolderId", ParameterType.Guid, usuariosHolderId.Value),
                    userIdParameter);
            }
        }

        private class UserFiltros
        {
            public Guid Id { get; private set; }
            public Guid? FabricaId { get; private set; }
            public Guid? ClienteId { get; private set; }
            public Guid? VendedorId { get; private set; }
            public Guid? TransportadoraId { get; private set; }

            public UserFiltros(Guid id, Guid? fabricaId, Guid? clienteId, Guid? vendedorId, Guid? transportadoraId)
            {
                Id = id;
                FabricaId = fabricaId;
                ClienteId = clienteId;
                VendedorId = vendedorId;
                TransportadoraId = transportadoraId;
            }
        }
    }
}
